package com.shen.jcbf.excelbf;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import com.shen.jcbf.datebf.DateTest;
import com.shen.jcbf.testutil.CityUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.shen.jcbf.strbf.Str2Py;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * 说明:
 * @author shenhx
 * @version 2017-5-16 下午3:31:49
 */
public class ReadExcel {

	/**
	 * 说明：
	 * @author shenhx
	 * @date 2017-5-16 下午3:31:50
	 * 
	 * @param args
	 * @throws IOException 
	 * @throws FileNotFoundException 
	 */
	public static void main(String[] args) {

		fydzDr();
		
	}
	
	public static void fydzDr(){
        
        //获取Excel文档的路径（这里Excel路径暂时固定，后期可能会作为参数获取。）
        String filePath = "/Users/lianxiao/个人/123.xls";
        
        String ajlxd = "";
        String ajlxz = "";

        String py = "";
        int xh = 1;
        String dateStr = DateTest.DateToStr(new Date());

        String corporateId = "28238";
        String corporateName = "地上铁租车（深圳）有限公司";
        Map<String,Long> cityMap = CityUtil.getCityMap();
        Map<String,String> carModelMap = CityUtil.getCarModelMap();

        try {
            // 创建对Excel工作簿文件，并得到xls文档第一页 “Sheet1”
            Workbook book = new XSSFWorkbook(new FileInputStream(filePath));
            Sheet sheet = book.getSheet("Sheet1");
            
            //获取到Excel文件中的所有行数  
            int rows = sheet.getPhysicalNumberOfRows();
            
            Cell cell;
            String cellStr;

            //遍历行  （这里 int i = 2 ,因为Excel文档第一、二行，为标题行，不读，所以从第三行开始读取）
            for (int i = 2; i < rows; i++) {
            	Row row = sheet.getRow(i);
                if (row != null) {
                    //得到有效列个数
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();

                    if(true){

                        //租金、押金、违章押金、营运保证金
                        Double zj = row.getCell(6).getNumericCellValue();
                        Double yj = row.getCell(7).getNumericCellValue();
                        Double wzyj = row.getCell(8).getNumericCellValue();
                        Double yybzj = row.getCell(9).getNumericCellValue();

                        //实际租金
                        int sjzj = zj.intValue() + yybzj.intValue();

                        String insertSql = "insert into corporate_has_car_model( corporate_id,  car_model_id,  city,  default_monthly_rent,  default_car_deposit,  default_illigal_deposit,  gmt_modify,  gmt_create,  city_id,  stock,  service_type) " +
                                "values(28238, "+carModelMap.get(row.getCell(2).getStringCellValue()+"----"+row.getCell(3).getStringCellValue())+", '"+
                                row.getCell(1).getStringCellValue()+"', "+
                                sjzj+"00, "+yj.intValue()+"00, "+wzyj.intValue()+"00, '"+dateStr+"', '"+dateStr+"', "+
                                cityMap.get(row.getCell(1).getStringCellValue())+", 0, 2);";

                        System.out.println(insertSql);
                        continue;
                    }

                    String name = cell.getStringCellValue();
                    
                    if( null != row.getCell(2) && !"".equals(row.getCell(2).getStringCellValue()) && !"　　".equals(row.getCell(1).getStringCellValue().substring(0,2))){
                    	name = name.substring(1,name.length()-2);
                    	System.out.println("insert into ajdzgz values('"+name+"','"+row.getCell(2).getStringCellValue()+"');");
                    	continue;
                    }
                    
                    if("　　".equals(name.substring(0,2))){
                    	name = name.substring(2,name.length()-2);
                    	/*
                    	py = Str2Py.cn2py(name);
                    	System.out.println( "INSERT INTO wsgz VALUES ("+ xh++ +", '案号不能为空', '"+name+"案件案号不能为空',"+
                    			" '案号', NULL, '"+ajlxd+"', '"+ajlxz+"', '"+name+"',"+
                    			" 'db_"+py+"', 't_"+py+"', 'c_baah', 'a.案号为空', NULL, "+
                    			"'select * from db_"+py + ".t_" +py+" where c_baah is null', NULL); " );
                    	*/
                    	System.out.println("insert into ajdzgz values('"+name+"','"+row.getCell(2).getStringCellValue()+"');");
                    }else if("　".equals(name.substring(0,1))){
                    	ajlxz = name.substring(1,name.length()-2);
                    }else{
                    	ajlxd = name.substring(0,name.length()-2);
                    }
                    
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
     
}
